package com.douma.dao.impl;

import com.douma.dao.ShopDao;
import com.douma.dto.Shop;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;


/**
 * 抖码课堂，专注中国程序员基础内功修炼
 * 官网：douma-tech.com
 * 公众号：抖码课堂
 *
 * @作者 : 老汤
 */

@Repository
public class ShopDaoImpl implements ShopDao {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private ResultSetExtractor<List<Shop>> resultSetExtractor = resultSet -> {
        List<Shop> shops = new ArrayList<>();
        while (resultSet.next()) {
            Shop shop = new Shop();
            shop.setId(resultSet.getLong(1));
            shop.setName(resultSet.getString(2));
            shop.setScore(resultSet.getInt(3));
            shop.setComments(resultSet.getInt(4));
            shop.setAvgPrice(resultSet.getLong(5));
            shop.setAddress(resultSet.getString(6));
            shop.setOpenHours(resultSet.getString(7));
            shop.setPhone(resultSet.getString(8));

            shops.add(shop);
        }
        return shops;
    };

    public Shop queryById(Long id) {
        logger.info("开始【数据库】查询商铺，商铺 id 为：" + id);
        String sql = "SELECT * FROM t_shop WHERE id = ?";

        List<Shop> shops = jdbcTemplate.query(sql, resultSetExtractor, id);

        if (shops.isEmpty()) {
            logger.info("【数据库】查询商铺结束，商铺 id 为：" + id + " 没有数据");
            return null;
        }

        Shop shop = shops.get(0);
        logger.info("【数据库】查询商铺结束，商铺 id 为：" + id);
        return shop;
    }

    @Override
    public List<Shop> queryAll() {
        String sql = "SELECT * FROM t_shop";
        return jdbcTemplate.query(sql, resultSetExtractor);
    }

    @Override
    public void incCommentsById(Long id) {
        String sql = "UPDATE t_shop SET comments = comments + 1 WHERE id = ?";
        jdbcTemplate.update(sql, id);
    }

    @Override
    public List<Long> queryAllIds() {
        return jdbcTemplate.queryForList("SELECT id FROM t_shop", Long.class);
    }

    @Override
    public Long insert(Shop shop) {
        String sql = "INSERT INTO t_shop (id, name, score, comments, avg_price, address, open_hours, phone) VALUES(NULL, ?, 0, 0, ?, ?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(dbConnection -> {
            PreparedStatement statement = dbConnection.prepareStatement(sql, new String[]{"id"});
            statement.setString(1, shop.getName());
            statement.setLong(2, shop.getAvgPrice());
            statement.setString(3, shop.getAddress());
            statement.setString(4, shop.getOpenHours());
            statement.setString(5, shop.getPhone());
            return statement;
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }
}